#include "sqlhelper.h"
#include <QSqlDatabase>
#include <QSqlQuery>
#include<QSqlRecord>
#include <QDebug>
#include <QSqlError>
#include <QMessageBox>
#include <QDateTime>
#include <QFileInfo>
#include "logger.h"

static const QMap<TableIndex, QString> tables = {
    {T_MATERIAL_CODE, "t_material_code"},
    {T_PARAMETER, "t_parameter"},
    {T_EXPERIMENT, "t_experiment"},
    {T_MATERIAL, "t_material"},
    {T_FLOW_TEST, "t_flow_test"},
    {T_VOLT_TEST, "t_volt_test"},
    {T_VERSION, "t_version"},
    {T_USER, "t_user"},
    {T_ROLE, "t_role"},
    {T_LOG, "t_log"},
};

SqlHelper * SqlHelper::instance = nullptr;
QSqlDatabase SqlHelper::db;

SqlHelper::SqlHelper()
{
}

bool SqlHelper::openDataBase(const QString &dbPath)
{
    //Create dir for database
    QFileInfo fileInfo(dbPath);
    QString path = fileInfo.path();
    createDir(path);
    qDebug() <<"connectDB" << dbPath;
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setHostName("localhost");
    db.setDatabaseName(dbPath);
    db.setUserName("root");
    db.setPassword("123456");
    bool ret = db.open();
    if (!ret)
    {
        QSqlError sqlError = db.lastError();
        QString text = sqlError.text();
        sysDebug("Sqlite connect fail: %s", text.data());
    }

    return ret;
}

void SqlHelper::closeDatabase()
{
    if (db.isOpen())
    {
        db.close();
    }
}

bool SqlHelper::setVersion(int major, int minor, int build)
{
    bool queryOK;
    QSqlQuery query;
    QString qsQuery = QString("delete from %1;").arg(tables[T_VERSION]);

    mutex.lock();
    query.prepare(qsQuery);
    queryOK = query.exec();
    if (queryOK)
    {
        qsQuery = QString("insert into %1(major, minor, build) values(:major, :minor, :build)").arg(tables[T_VERSION]);
        query.prepare(qsQuery);
        query.bindValue(":major", major);
        query.bindValue(":minor", minor);
        query.bindValue(":build", build);
        queryOK = query.exec();
        if (!queryOK)
        {
            qDebug() << query.lastError().text();
            qDebug() << qsQuery;
        }
    }
    else
    {
        qDebug() << query.lastError().text();
        qDebug() << qsQuery;
    }

    mutex.unlock();
    return queryOK;
}

bool SqlHelper::version(int &major, int &minor, int &build)
{
    major = minor = build = 0;

    mutex.lock();
    QSqlQuery query;
    QString qsQuery = QString("select * from %1;").arg(tables[T_VERSION]);
    query.prepare(qsQuery);
    bool queryOK = query.exec();
    mutex.unlock();
    if (queryOK && query.next())
    {
        major = query.value(0).toInt();
        minor = query.value(1).toInt();
        build = query.value(2).toInt();
    }

    return queryOK;
}

SqlHelper::~SqlHelper()
{
    if (instance != nullptr)
    {
        closeDatabase();
        qDebug() << "delete SqlHelper instance";
        delete instance;
    }
}

SqlHelper *SqlHelper::getInstance()
{
    static QMutex mutex;
    if(!instance)
    {
        QMutexLocker locker(&mutex);
        if(!instance)
        {
            instance = new SqlHelper();
        }
    }
    return instance;
}

QList<QStringList> SqlHelper::selectAllFromTable(QString table)
{
    QList<QStringList> listInfo;
    QString qsQuery = QString("select * from %1").arg(table);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    listInfo = selectFromTable(query);
    mutex.unlock();
    return listInfo;
}

QList<User> SqlHelper::selectAllUsers()
{
    QList<User> infoList;
    QString table = tables[T_USER];
    QList<QStringList> listInfo = selectAllFromTable(table);
    for (int i = 0;i < listInfo.size();i++) {
        User tmp = convertSL2User(listInfo[i]);
        infoList.append(tmp);
    }
    return infoList;
}

QList<Role> SqlHelper::selectAllRoles()
{
    QList<Role> infoList;
    QString table = tables[T_ROLE];
    QList<QStringList> listInfo = selectAllFromTable(table);
    for (int i = 0;i < listInfo.size();i++) {
        Role tmp = convertSL2Role(listInfo[i]);
        infoList.append(tmp);
    }
    return infoList;
}

QList<QStringList> SqlHelper::selectFromTable(QSqlQuery &query)
{
    QList<QStringList> listInfo;
    bool queryOK = query.exec();
    if (queryOK)
    {
        while (query.next()) {
            QStringList rowData;
            QSqlRecord rec = query.record();
            int colNnum = rec.count();
            for (int i = 0; i < colNnum;i++) {
                rowData << query.value(i).toString();
            }
            listInfo.append(rowData);
        }
    }
    else
    {
        QSqlError sqlError = query.lastError();
        qDebug() << sqlError.text();
    }

    return listInfo;
}

EttaError SqlHelper::updateTable(QString qsQuery)
{
    EttaError ret = EttaError::OK;
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    bool queryOK = query.exec();
    if(queryOK)
    {
        ret= EttaError::OK;
    }
    else
    {
        qDebug() << query.lastError().text();
        qDebug() << qsQuery;
        ret = EttaError::UPDATE_TABLE_ERROR;
    }
    mutex.unlock();
    return ret;
}

EttaError SqlHelper::updateTable(QSqlQuery query)
{
    EttaError ret = EttaError::OK;

    bool queryOK = query.exec();
    if(queryOK)
    {
        ret= EttaError::OK;
    }
    else
    {
        qDebug() << query.lastError().text();
        ret = EttaError::UPDATE_TABLE_ERROR;
    }

    return ret;
}

EttaError SqlHelper::insertTable(QSqlQuery &query)
{
    EttaError ret = EttaError::OK;
    bool queryOK = query.exec();
    if(queryOK)
    {
        ret= EttaError::OK;
    }
    else
    {
        qDebug() << query.lastError().text();
        ret = EttaError::INSERT_TABLE_ERROR;
    }
    return ret;
}

EttaError SqlHelper::checkLogInfo(QString user, QString pw)
{
    EttaError checkRes = EttaError::OK;
    checkRes =  checkLoginUserStatus(user);
    if(EttaError::OK != checkRes)
    {
        return checkRes;
    }
    QString qsQuery = QString("select * from %1 where name = ? and password = ?").arg(tables[T_USER]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0,user);
    query.bindValue(1,pw);
    QList<QStringList> info = selectFromTable(query);
    mutex.unlock();
    checkRes=info.size() > 0 ? EttaError::OK : EttaError::LOGIN_USER_PW_MISMATCH;
    return checkRes;
}

EttaError SqlHelper::checkLoginUserStatus(QString username)
{
    EttaError checkRes = EttaError::OK;
    User user;
    user.name = username;
    QList<User> info = selectUserBasedOnName(user);

    int count = info.size();
    if(count > 0)
    {
        User tmp = info[0];
        if(!tmp.status)
        {
            sysDebug("User: %s is disabled", username.toLatin1().data());
            checkRes = EttaError::LOGIN_USER_DISABLED;
        }
        else if (tmp.failLoginTimes >= MAX_WRONG_PW_TIMES)
        {
            sysDebug("User: %s is locked", username.toLatin1().data());
            checkRes = EttaError::LOGIN_ACCOUNT_LOCKED;
        }
    }
    else
    {
        sysDebug("User: %s not exists", username.toLatin1().data());
        checkRes = EttaError::LOGIN_NO_USER;
    }
    return checkRes;
}

//Avoid SQL Injection Attack
EttaError SqlHelper::insertParameterItem(Parameter p)
{
    EttaError ret = EttaError::OK;
    QString qsQuery = QString("INSERT INTO %1 VALUES (?,?,?,?,?,?,?,?)").arg(tables[T_PARAMETER]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0,p.name);
    query.bindValue(1,p.code);
    query.bindValue(2,p.volt);
    query.bindValue(3,p.pulse);
    query.bindValue(4,p.interval);
    query.bindValue(5,p.speed);
    query.bindValue(6,p.times);
    query.bindValue(7,p.ison);
    ret = insertTable(query);
    mutex.unlock();
    return ret;
}

QList<QStringList> SqlHelper::parameterEnableCount()
{
    QList<QStringList> listInfo;
    QString qsQuery = QString("select * from %1 where ison=1").arg(tables[T_PARAMETER]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    bool queryOK = query.exec();
    if(queryOK)
    {
        while (query.next()) {
            QStringList rowData;
            QSqlRecord rec = query.record();
            int colNnum = rec.count();
            for (int i =0; i < colNnum;i++) {
                rowData << query.value(i).toString();
            }
            listInfo.append(rowData);
        }
    }
    mutex.unlock();
    return listInfo;
}

QList<QStringList> SqlHelper::selectParameterName(Parameter p)
{
    QList<QStringList> listInfo;
    QString qsQuery = QString("select * from %1 where name=?").arg(tables[T_PARAMETER]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0,p.name);
    bool queryOK = query.exec();
    if(queryOK)
    {
        while (query.next()) {
            QStringList rowData;
            QSqlRecord rec = query.record();
            int colNnum = rec.count();
            for (int i =0; i < colNnum;i++) {
                rowData << query.value(i).toString();
            }
            listInfo.append(rowData);
        }
    }
    mutex.unlock();
    return listInfo;
}

QList<QStringList> SqlHelper::selectParameterCode(Parameter p)
{
    QList<QStringList> listInfo;
    QString qsQuery = QString("select * from %1 where code=?").arg(tables[T_PARAMETER]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0,p.code);
    bool queryOK = query.exec();
    if(queryOK)
    {
        while (query.next()) {
            QStringList rowData;
            QSqlRecord rec = query.record();
            int colNnum = rec.count();
            for (int i =0; i < colNnum;i++) {
                rowData << query.value(i).toString();
            }
            listInfo.append(rowData);
        }
    }
    mutex.unlock();
    return listInfo;
}

QList<QStringList> SqlHelper::selectParameterIson(Parameter p)
{
    QList<QStringList> listInfo;
    QString qsQuery = QString("select * from %1 where ison=?").arg(tables[T_PARAMETER]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0,p.ison);
    bool queryOK = query.exec();
    if(queryOK)
    {
        while (query.next()) {
            QStringList rowData;
            QSqlRecord rec = query.record();
            int colNnum = rec.count();
            for (int i =0; i < colNnum;i++) {
                rowData << query.value(i).toString();
            }
            listInfo.append(rowData);
        }
    }
    mutex.unlock();
    return listInfo;
}

EttaError SqlHelper::updateParameterIson(Parameter p)
{
    EttaError ret = EttaError::OK;
    QList<QStringList> listInfo;
    QString qsQuery = QString("update %1 set ison = ? where name=?").arg(tables[T_PARAMETER]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0,p.ison);
    query.bindValue(1,p.name);
    bool queryOK = query.exec();
    if(queryOK)
    {
        qDebug()<<"Update t_parameter OK.";
    }
    else
    {
        qDebug()<<"Update t_parameter NG.";
        QSqlError sqlError = query.lastError();
        qDebug() << sqlError.text();
    }
    mutex.unlock();
    return ret;
}

EttaError SqlHelper::updateParameterBasedOnName(Parameter p)
{
    EttaError ret = EttaError::OK;
    QList<QStringList> listInfo;
    QString qsQuery = QString("update %1"
                              " set code = ?, volt = ?, pulse = ?, interval = ?, speed = ?, times = ?, ison = ?"
                              " where name = ?").arg(tables[T_PARAMETER]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0,p.code);
    query.bindValue(1,p.volt);
    query.bindValue(2,p.pulse);
    query.bindValue(3,p.interval);
    query.bindValue(4,p.speed);
    query.bindValue(5,p.times);
    query.bindValue(6,p.ison);
    query.bindValue(7,p.name);
    bool queryOK = query.exec();
    if(queryOK)
    {
        qDebug()<<"Update t_parameter OK.";
    }
    else
    {
        qDebug()<<"Update t_parameter NG.";
        QSqlError sqlError = query.lastError();
        qDebug() << sqlError.text();
    }
    mutex.unlock();
    return ret;
}

EttaError SqlHelper::deleteParameterItem(QString name)
{
    EttaError ret = EttaError::OK;
    QString qsQuery = QString("delete from %1 where name=?").arg(tables[T_PARAMETER]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0,name);
    ret = updateTable(query);
    mutex.unlock();
    return  ret;
}

EttaError SqlHelper::createTable(TableIndex index)
{
    EttaError ret = EttaError::OK;
    QString qsQuery = QString();
    switch (index)
    {
    case TableIndex::T_VERSION:
        qsQuery = QString("CREATE TABLE IF NOT EXISTS [%1] ("
                          "[major] TEXT(128),"
                          "[minor] TEXT(128),"
                          "[build] TEXT(128));").arg(tables[index]);
        break;
    case TableIndex::T_USER:
        qsQuery = QString("CREATE TABLE IF NOT EXISTS [%1] ("
                          "[name] TEXT PRIMARY KEY NOT NULL,"
                          "[password] TEXT NOT NULL,"
                          "[role] TEXT NOT NULL,"
                          "[permission] INT NOT NULL,"
                          "[status] INT NOT NULL,"
                          "[create_datetime] TEXT,"
                          "[remark] TEXT,"
                          "[fail_login_times] INT NOT NULL DEFAULT 0);").arg(tables[index]);
        break;
    case TableIndex::T_ROLE:
        qsQuery = QString("CREATE TABLE IF NOT EXISTS [%1] ("
                          "[name] TEXT PRIMARY KEY NOT NULL,"
                          "[permission] INT NOT NULL,"
                          "[create_datetime] TEXT);").arg(tables[index]);
        break;
    case TableIndex::T_PARAMETER:
        qsQuery = QString("CREATE TABLE IF NOT EXISTS [%1] ("
                          "[name] TEXT PRIMARY KEY NOT NULL,"
                          "[code] TEXT NOT NULL,"
                          "[volt] REAL NOT NULL,"
                          "[pulse] REAL NOT NULL,"
                          "[interval] REAL NOT NULL,"
                          "[speed] REAL NOT NULL,"
                          "[times] REAL NOT NULL,"
                          "[ison] BOOL NOT NULL);").arg(tables[index]);
        break;
    case TableIndex::T_EXPERIMENT:
        qsQuery = QString("CREATE TABLE IF NOT EXISTS [%1] ("
                          "[username] TEXT NOT NULL,"
                          "[datetimeStart] TEXT PRIMARY KEY NOT NULL,"
                          "[datetimeEnd] TEXT NOT NULL,"
                          "[parameter_name] TEXT NOT NULL,"
                          "[parameter_code] TEXT NOT NULL,"
                          "[sample_isn] TEXT NOT NULL,"
                          "[material_isn] TEXT NOT NULL,"
                          "[duration] TEXT NOT NULL,"
                          "[result] TEXT NOT NULL,"
                          "[volume] REAL NOT NULL DEFAULT 0,"
                          "[voltage_rms_datapath] TEXT);").arg(tables[index]);
        break;
    case TableIndex::T_MATERIAL:
        qsQuery = QString("CREATE TABLE IF NOT EXISTS [%1] ("
                          "[serial] TEXT PRIMARY KEY NOT NULL,"
                          "[name] TEXT NOT NULL,"
                          "[spec] TEXT NOT NULL,"
                          "[lot] TEXT NOT NULL,"
                          "[expdate] TEXT NOT NULL);").arg(tables[index]);
        break;
    case TableIndex::T_LOG:
        qsQuery = QString("CREATE TABLE IF NOT EXISTS [%1] ("
                          "[datetime] TEXT NOT NULL,"
                          "[username] TEXT NOT NULL,"
                          "[type] INT NOT NULL,"
                          "[detail] TEXT NOT NULL);").arg(tables[index]);
        break;
    case TableIndex::T_FLOW_TEST:
        qsQuery = QString("CREATE TABLE IF NOT EXISTS [%1] ("
                          "[datetime] TEXT PRIMARY KEY NOT NULL,"
                          "[username] TEXT NOT NULL,"
                          "[volume] REAL NOT NULL DEFAULT 0,"
                          "[flow_set] REAL NOT NULL DEFAULT 0,"
                          "[flow_real] REAL NOT NULL DEFAULT 0);").arg(tables[index]);
        break;
    case TableIndex::T_VOLT_TEST:
        qsQuery = QString("CREATE TABLE IF NOT EXISTS [%1] ("
                          "[datetime] TEXT PRIMARY KEY NOT NULL,"
                          "[username] TEXT NOT NULL,"
                          "[low_volt_set] REAL NOT NULL DEFAULT 0,"
                          "[low_volt_real] REAL NOT NULL DEFAULT 0,"
                          "[medium_volt_set] REAL NOT NULL DEFAULT 0,"
                          "[medium_volt_real] REAL NOT NULL DEFAULT 0,"
                          "[high_volt_set] REAL NOT NULL DEFAULT 0,"
                          "[high_volt_real] REAL NOT NULL DEFAULT 0,"
                          "[low_pulse_width_set] REAL NOT NULL DEFAULT 0,"
                          "[low_pulse_width_real] REAL NOT NULL DEFAULT 0,"
                          "[medium_pulse_width_set] REAL NOT NULL DEFAULT 0,"
                          "[medium_pulse_width_real] REAL NOT NULL DEFAULT 0,"
                          "[high_pulse_width_set] REAL NOT NULL DEFAULT 0,"
                          "[high_pulse_width_real] REAL NOT NULL DEFAULT 0);").arg(tables[index]);
        break;
    case TableIndex::T_MATERIAL_CODE:
        qsQuery = QString("CREATE TABLE IF NOT EXISTS [%1] ("
                          "[qrcode] TEXT NOT NULL);").arg(tables[index]);
        break;
    default:
        break;
    }

    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    bool queryOK = query.exec();
    if (!queryOK)
    {
        QSqlError sqlError = query.lastError();
        qDebug() << "Table created NG." << sqlError.text();
        qDebug() << qsQuery;
        ret = EttaError::CREATE_TABLE_ERROR;
    }
    mutex.unlock();
    return ret;
}

EttaError SqlHelper::insertUser(User user)
{
    EttaError ret = EttaError::OK;
    QString qsQuery = QString("insert into %1(name, password, role, permission, status, create_datetime, remark, fail_login_times)"
                              " select :name, :password, :role, :permission, :status, :create_datetime, :remark, :fail_login_times"
                              " where not exists(select 1 from %1 where name=:name)").arg(tables[T_USER]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(":name", user.name);
    query.bindValue(":password", user.password);
    query.bindValue(":role", user.role);
    query.bindValue(":permission", user.permission);
    query.bindValue(":status", user.status);
    query.bindValue(":create_datetime", user.createDatetime);
    query.bindValue(":remark", user.remark);
    query.bindValue(":fail_login_times", user.failLoginTimes);
    bool queryOK = query.exec();
    if (!queryOK)
    {
        QSqlError sqlError = query.lastError();
        qDebug() << "Insert user NG." << sqlError.text();
        qDebug() << qsQuery;
        ret = EttaError::USER_INSERT_FAIL;
    }
    mutex.unlock();
    return ret;
}

int SqlHelper::selectUserPermission(User user)
{
    int permission =0;
    QList<QStringList> listInfo;
    QString qsQuery = QString("select * from %1 where name = ?").arg(tables[T_USER]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0, user.name);
    bool queryOK = query.exec();
    if(queryOK)
    {
        while (query.next()) {
            QStringList rowData;
            QSqlRecord rec = query.record();
            int colNnum = rec.count();
            for (int i =0; i < colNnum;i++) {
                rowData << query.value(i).toString();
            }
            listInfo.append(rowData);
        }
    }
    mutex.unlock();
    if(listInfo.size()>0)
    {
        permission = listInfo[0].at(3).toInt();
    }
    return permission;
}

EttaError SqlHelper::updateUserRole(User user, Role role)
{
    EttaError ret = EttaError::OK;
    QString qsQuery = QString("update %1 set role = ?, permission = ? where name = ?").arg(tables[T_USER]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0, role.name);
    query.bindValue(1, role.permission);
    query.bindValue(2, user.name);
    ret = updateTable(query);
    mutex.unlock();
    return ret;
}

EttaError SqlHelper::setUserPassword(User user)
{
    EttaError ret = EttaError::OK;
    QString qsQuery = QString("update %1 set password = ? where name = ?").arg(tables[T_USER]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0, user.password);
    query.bindValue(1, user.name);
    ret = updateTable(query);
    mutex.unlock();
    return ret;
}

EttaError SqlHelper::deleteUser(User user)
{
    EttaError ret = EttaError::OK;
    //delete from t_user where name='test';
    QString qsQuery = QString("delete from %1 where name = ?").arg(tables[T_USER]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0, user.name);
    ret = updateTable(query);
    if (ret != EttaError::OK)
    {
        QSqlError sqlError = query.lastError();
        qDebug()<<"Delete user NG." << sqlError.text();
        ret = EttaError::USER_DELETE_FAIL;
    }

    mutex.unlock();
    return ret;
}

User SqlHelper::convertSL2User(QStringList sl)
{
    User user;
    user.name = sl.at(0);
    user.password = sl.at(1);
    user.role = sl.at(2);
    user.permission = sl.at(3).toInt();
    user.status = sl.at(4).toInt();
    user.createDatetime = sl.at(5);
    user.remark = sl.at(6);
    user.failLoginTimes = sl.at(7).toInt();
    return user;
}

EttaError SqlHelper::setUserEnable(User user)
{
    EttaError ret = EttaError::OK;
    QString qsQuery = QString("update %1 set status = ? where name = ?").arg(tables[T_USER]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0,user.status);
    query.bindValue(1,user.name);
    ret = updateTable(query);
    mutex.unlock();
    return ret;
}

EttaError SqlHelper::setFailLoginTimes(User user)
{
    EttaError ret = EttaError::OK;
    QString qsQuery = QString("update %1 set fail_login_times = ? where name = ?").arg(tables[T_USER]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0,user.failLoginTimes);
    query.bindValue(1,user.name);
    ret = updateTable(query);
    mutex.unlock();
    return ret;
}

QList<User> SqlHelper::selectUserBasedOnName(User user)
{
    QList<User> info;
    QList<QStringList> listInfo;
    QString qsQuery = QString("select * from %1 where name = ?").arg(tables[T_USER]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0,user.name);
    listInfo = selectFromTable(query);
    mutex.unlock();

    for (int i = 0;i < listInfo.size();i++) {
        User tmp = convertSL2User(listInfo[i]);
        info.append(tmp);
    }
    return info;
}

QList<User> SqlHelper::selectUserBasedOnRole(QString roleName)
{
    QList<User> info;
    QList<QStringList> listInfo;
    QString qsQuery = QString("select * from %1 where role = ?").arg(tables[T_USER]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0,roleName);
    listInfo = selectFromTable(query);
    mutex.unlock();

    for (int i = 0;i < listInfo.size();i++) {
        User tmp = convertSL2User(listInfo[i]);
        info.append(tmp);
    }
    return info;
}

bool SqlHelper::checkUserIsOn(const QString &userName)
{
    QString qsQuery = QString("select count(*) from %1 where name = ? and status = 1").arg(tables[T_USER]);

    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0, userName);

    return queryRowsCount(query) > 0;
}

bool SqlHelper::checkUserExist(const QString &userName)
{
    QString qsQuery = QString("select count(*) from %1 where name = ?").arg(tables[T_USER]);

    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0, userName);

    return queryRowsCount(query) > 0;
}

bool SqlHelper::checkRoleHasUser(const QString roleName)
{
    QString qsQuery = QString("select count(*) from %1 where role = ?").arg(tables[T_USER]);

    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0, roleName);

    return queryRowsCount(query) > 0;
}

EttaError SqlHelper::insertRole(Role role)
{
    EttaError ret = EttaError::OK;
    QString qsQuery = QString("insert into %1(name, permission, create_datetime)"
                              " select :name, :permission, :create_datetime"
                              " where not exists(select 1 from %1 where name=:name)").arg(tables[T_ROLE]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(":name", role.name);
    query.bindValue(":permission", role.permission);
    query.bindValue(":create_datetime", role.createDatetime);
    bool queryOK = query.exec();
    if (!queryOK)
    {
        QSqlError sqlError = query.lastError();
        qDebug()<<"Insert role NG." << sqlError.text();
        ret = EttaError::ROLE_INSERT_FAIL;
    }
    mutex.unlock();
    return ret;
}

EttaError SqlHelper::deleteRole(Role role)
{
    EttaError ret = EttaError::OK;
    QString qsQuery = QString("delete from %1 where name = ?").arg(tables[T_ROLE]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0,role.name);
    bool queryOK = query.exec();
    if (!queryOK)
    {
        QSqlError sqlError = query.lastError();
        qDebug()<<"Delete role NG." << sqlError.text();
        ret = EttaError::ROLE_DELETE_FAIL;
    }
    mutex.unlock();
    return ret;
}

int SqlHelper::selectRolePermission(Role role)
{
    int permissionFlag = 0;
    QList<QStringList> listInfo;
    QString qsQuery = QString("select * from %1 where name = ?").arg(tables[T_ROLE]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0, role.name);
    bool queryOK = query.exec();
    if(queryOK)
    {
        while (query.next()) {
            QStringList rowData;
            QSqlRecord rec = query.record();
            int colNnum = rec.count();
            for (int i =0; i < colNnum;i++) {
                rowData << query.value(i).toString();
            }
            listInfo.append(rowData);
        }
    }
    mutex.unlock();
    if(listInfo.size()>0)
    {
        permissionFlag = listInfo[0].at(1).toInt();
    }
    return permissionFlag;
}

EttaError SqlHelper::updateRole(Role role)
{
    EttaError ret = EttaError::OK;
    QString qsQuery = QString("update %1 set permission = ? where name = ?").arg(tables[T_ROLE]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0,role.permission);
    query.bindValue(1,role.name);
    ret = updateTable(query);
    mutex.unlock();

    // update user's role permission
    qsQuery = QString("update %1 set permission = ? where role = ?").arg(tables[T_USER]);
    mutex.lock();
    QSqlQuery query2;
    query2.prepare(qsQuery);
    query2.bindValue(0,role.permission);
    query2.bindValue(1,role.name);
    ret = updateTable(query2);
    mutex.unlock();

    return ret;
}

EttaError SqlHelper::updateUserPermission2Zero(Role role)
{
    EttaError ret = EttaError::OK;
    QString qsQuery = QString("update %1 set permission = 0 where role = ?").arg(tables[T_USER]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0,role.name);
    ret = updateTable(query);
    mutex.unlock();
    return ret;
}

QList<Role> SqlHelper::selectRoleName(Role role)
{
    QList<QStringList> listInfo;
    QList<Role> listRole;
    QString qsQuery = QString("select * from %1 where name = ?").arg(tables[T_ROLE]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0, role.name);
    bool queryOK = query.exec();
    if(queryOK)
    {
        while (query.next()) {
            QStringList rowData;
            QSqlRecord rec = query.record();
            int colNnum = rec.count();
            for (int i =0; i < colNnum;i++) {
                rowData << query.value(i).toString();
            }
            listInfo.append(rowData);
        }
    }
    mutex.unlock();
    int count = listInfo.size();
    if(count>0)
    {
        for (int i = 0;i < count;i++) {
            listRole.append(convertSL2Role(listInfo[0]));
        }
    }
    return listRole;
}

bool SqlHelper::checkRoleExist(const QString &roleName)
{
    QString qsQuery = QString("select count(*) from %1 where name = ?").arg(tables[T_ROLE]);

    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0, roleName);

    return queryRowsCount(query) > 0;
}

Role SqlHelper::convertSL2Role(QStringList sl)
{
    Role r;
    r.name = sl.at(0);
    r.permission = sl.at(1).toInt();
    r.createDatetime = sl.at(2);
    return r;
}

Parameter SqlHelper::convertSL2Parameter(QStringList sl)
{
    Parameter p;
    p.name = sl.at(ParameterIndex::PARAM_NAME);
    p.code = sl.at(ParameterIndex::PARAM_CODE);
    p.volt = sl.at(ParameterIndex::PARAM_VOLT).toDouble();
    p.pulse = sl.at(ParameterIndex::PARAM_PULSE).toDouble();
    p.interval = sl.at(ParameterIndex::PARAM_INTERVAL).toDouble();
    p.speed = sl.at(ParameterIndex::PARAM_SPEED).toDouble();
    p.times = sl.at(ParameterIndex::PARAM_TIMES).toDouble();
    p.ison = sl.at(ParameterIndex::PARAM_ISON).toInt();
    return p;
}

QList<Parameter> SqlHelper::selectParameter(Parameter p)
{
    QList<Parameter> pList;
    QList<QStringList> info = selectParameterName(p);
    int count = info.size();
    for (int i =0; i < count;i++) {
        pList.append(convertSL2Parameter(info[i]));
    }
    return pList;
}

bool SqlHelper::checkParamNameExist(const QString &paramName)
{
    QString qsQuery = QString("select count(*) from %1 where name = ?").arg(tables[T_PARAMETER]);

    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0, paramName);

    return queryRowsCount(query) > 0;
}

bool SqlHelper::checkParamCodeExist(const QString &paramCode)
{
    QString qsQuery = QString("select count(*) from %1 where code = ?").arg(tables[T_PARAMETER]);

    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0, paramCode);

    return queryRowsCount(query) > 0;
}

bool SqlHelper::checkParamIson(const QString &paramName)
{
    QString qsQuery = QString("select count(*) from %1 where name = ? and ison = 1").arg(tables[T_PARAMETER]);

    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0, paramName);

    return queryRowsCount(query) > 0;
}

bool SqlHelper::checkSampleNameExist(const QString sampleName)
{
    QString qsQuery = QString("select count(*) from %1 where sample_isn = ?").arg(tables[T_EXPERIMENT]);

    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0, sampleName);

    return queryRowsCount(query) > 0;
}

EttaError SqlHelper::insertMaterial(Material m)
{
    EttaError ret = EttaError::OK;
    QString qsQuery = QString("insert into %1 VALUES(?,?,?,?,?)").arg(tables[T_MATERIAL]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0,m.serialCode);
    query.bindValue(1,m.name);
    query.bindValue(2,m.type);
    query.bindValue(3,m.batchNumber);
    query.bindValue(4,m.expirationDate);
    ret = insertTable(query);
    mutex.unlock();
    return ret;
}

EttaError SqlHelper::insertMaterial(const QString &qrcode)
{
    EttaError ret = EttaError::OK;
    QString qsQuery = QString("insert into %1 VALUES(?)").arg(tables[T_MATERIAL_CODE]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0, qrcode);
    ret = insertTable(query);
    mutex.unlock();
    return ret;
}

QList<QStringList> SqlHelper::selectMaterialSerial(Material m)
{
    QList<QStringList> listInfo;
    QString qsQuery = QString("select * from %1 where serial=?").arg(tables[T_MATERIAL]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0,m.serialCode);
    listInfo = selectFromTable(query);
    mutex.unlock();
    return listInfo;
}

bool SqlHelper::checkMaterialExist(const QString &qrcode)
{
    QString qsQuery = QString("select count(*) from %1 where qrcode=?").arg(tables[T_MATERIAL_CODE]);

    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0, qrcode);

    return queryRowsCount(query) > 0;
}

int SqlHelper::queryRowsCount(QSqlQuery &query)
{
    int count = 0;

    mutex.lock();
    bool queryOK = query.exec();
    if (queryOK)
    {
        if (query.next() && query.record().count() > 0)
        {
            count = query.value(0).toInt();
        }
    }
    else
    {
        QSqlError sqlError = query.lastError();
        qDebug() << query.executedQuery();
        qDebug() << sqlError.text();
    }

    mutex.unlock();
    return count;
}

EttaError SqlHelper::insertExperimentInfo(const ExperimentInfo &expInfo)
{
    EttaError ret = EttaError::OK;
    QString qsQuery = QString("INSERT INTO %1 VALUES(?,?,?,?,?,?,?,?,?,?,?)").arg(tables[T_EXPERIMENT]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(EXP_USERNAME, expInfo.username);
    query.bindValue(EXP_DATETIME_START, expInfo.datetimeExpStart);
    query.bindValue(EXP_DATETIME_END, expInfo.datetimeExpEnd);
    query.bindValue(EXP_PARAMETER_NAME, expInfo.parameterName);
    query.bindValue(EXP_PARAMETER_CODE, expInfo.parameterCode);
    query.bindValue(EXP_SAMPLE_NAME, expInfo.sampleName);
    query.bindValue(EXP_MATERIAL_ISN, expInfo.materialISN);
    query.bindValue(EXP_DURATION, expInfo.duration);
    query.bindValue(EXP_RESULT, expInfo.result);
    query.bindValue(EXP_VOLUME, expInfo.volume);
    query.bindValue(EXP_DATAPATH, expInfo.voltageRmsDatapath);
    ret = insertTable(query);
    mutex.unlock();
    return ret;
}

QList<ExperimentInfo> SqlHelper::selectExperiment(QString startTime, QString endTime,
                                                  QString username, QString parameterName,
                                                  QString result, QString sampleID,
                                                  QString materialBatchNo, QString materialSpec)
{
        QString qsQuery = QString(
                          "select %1.*, %2.spec, %2.lot, %3.volt, %3.pulse from %1"
                          " inner join %2 on %2.serial = %1.material_isn"
                          " inner join %3 on %3.name = %1.parameter_name "
                          " where DATETIME(datetimeStart) >= '").arg(tables[T_EXPERIMENT]).arg(tables[T_MATERIAL]).arg(tables[T_PARAMETER]);
        qsQuery += startTime;
        qsQuery += "' AND DATETIME(datetimeStart) <= '";
        qsQuery += endTime + "'";

        if (!username.isEmpty()) {
            if (username.startsWith("!="))
            {
                qsQuery += " AND username != '";
                qsQuery += username.replace("!=", "") + "'";
            }
            else
            {
                qsQuery += " AND username = '";
                qsQuery += username + "'";
            }
        }
        if (!parameterName.isEmpty()) {
            qsQuery += " AND parameter_name = '";
            qsQuery += parameterName + "'";
        }
        if (!result.isEmpty()) {
            qsQuery += " AND result = '";
            qsQuery += result + "'";
        }
        if (!sampleID.isEmpty()) {
            qsQuery += " AND sample_isn LIKE '%";
            qsQuery += sampleID + "%'";
        }
        if (!materialBatchNo.isEmpty()) {
            qsQuery += QString(" AND %1.lot LIKE '%").arg(tables[T_MATERIAL]);
            qsQuery += materialBatchNo + "%'";
        }
        if (!materialSpec.isEmpty()) {
            qsQuery += QString(" AND %1.spec LIKE '%").arg(tables[T_MATERIAL]);
            qsQuery += materialSpec + "%'";
        }
        qsQuery += ";";

        qDebug() << "qsQuery :" << qsQuery;

        QList<ExperimentInfo> expList;
        QList<QStringList> listInfo;
        mutex.lock();
        QSqlQuery query;
        query.prepare(qsQuery);

        listInfo = selectFromTable(query);
        mutex.unlock();

        for (int i = 0;i < listInfo.size();i++) {
            ExperimentInfo tmp = convertSL2ExperimentInfo(listInfo[i]);
            expList.append(tmp);
        }
        return expList;
}

ExperimentInfo SqlHelper::convertSL2ExperimentInfo(QStringList sl)
{
    ExperimentInfo expInfo;
    expInfo.username = sl.at(EXP_USERNAME);
    expInfo.datetimeExpStart = sl.at(EXP_DATETIME_START);
    expInfo.datetimeExpEnd = sl.at(EXP_DATETIME_END);
    expInfo.parameterName = sl.at(EXP_PARAMETER_NAME);
    expInfo.parameterCode = sl.at(EXP_PARAMETER_CODE);
    expInfo.sampleName = sl.at(EXP_SAMPLE_NAME);
    expInfo.materialISN = sl.at(EXP_MATERIAL_ISN);
    expInfo.duration = sl.at(EXP_DURATION);
    expInfo.result = sl.at(EXP_RESULT);
    expInfo.volume = sl.at(EXP_VOLUME).toDouble();
    expInfo.voltageRmsDatapath = sl.at(EXP_DATAPATH);
    expInfo.specifications = sl.at(EXP_SPECIFICATIONS);
    expInfo.batchNumber = sl.at(EXP_BATCH_NUMBER);
    expInfo.voltage = sl.at(EXP_VOLTAGE).toInt();
    expInfo.pulse = sl.at(EXP_PULSE).toInt();
    return expInfo;
}

EttaError SqlHelper::insertVoltTestResult(VoltTest vt)
{
    EttaError ret = EttaError::OK;
    QString qsQuery = QString("INSERT INTO %1 VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)").arg(tables[T_VOLT_TEST]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(VOLT_DATETIME, vt.datetime);
    query.bindValue(VOLT_USERNAME, vt.username);
    query.bindValue(VOLT_LOW_VOLT_SET, vt.lowVolt.setValue);
    query.bindValue(VOLT_LOW_VOLT_REAL, vt.lowVolt.realValue);
    query.bindValue(VOLT_MEDIUM_VOLT_SET, vt.mediumVolt.setValue);
    query.bindValue(VOLT_MEDIUM_VOLT_REAL, vt.mediumVolt.realValue);
    query.bindValue(VOLT_HIGH_VOLT_SET, vt.highVolt.setValue);
    query.bindValue(VOLT_HIGH_VOLT_REAL, vt.highVolt.realValue);
    query.bindValue(VOLT_LOW_PULSE_WIDTH_SET, vt.lowPulseWidth.setValue);
    query.bindValue(VOLT_LOW_PULSE_WIDTH_REAL, vt.lowPulseWidth.realValue);
    query.bindValue(VOLT_MEDIUM_PULSE_WIDTH_SET, vt.mediumPulseWidth.setValue);
    query.bindValue(VOLT_MEDIUM_PULSE_WIDTH_REAL, vt.mediumPulseWidth.realValue);
    query.bindValue(VOLT_HIGH_PULSE_WIDTH_SET, vt.highPulseWidth.setValue);
    query.bindValue(VOLT_HIGH_PULSE_WIDTH_REAL, vt.highPulseWidth.realValue);
    ret = insertTable(query);
    mutex.unlock();
    return ret;
}

QList<VoltTest> SqlHelper::selectVoltTestBasedOnTime(QString startTime, QString endTime, QString user)
{
    QList<VoltTest> voltList;
    QList<QStringList> listInfo;
    QString qsQuery = QString("select * from %1 where DATETIME(datetime) >= ? AND DATETIME(datetime) <= ?").arg(tables[T_VOLT_TEST]);
    if (!user.isEmpty()) {
        if (user.startsWith("!="))
        {
            qsQuery += " AND username != '";
            qsQuery += user.replace("!=", "") + "'";
        }
        else
        {
            qsQuery += " AND username = '";
            qsQuery += user + "'";
        }
    }

    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0,startTime);
    query.bindValue(1,endTime);
    listInfo = selectFromTable(query);
    mutex.unlock();

    for (int i = 0;i < listInfo.size();i++) {
        VoltTest tmp = convertSL2VoltTest(listInfo[i]);;
        voltList.append(tmp);
    }
    return voltList;
}

VoltTest SqlHelper::convertSL2VoltTest(QStringList sl)
{
    VoltTest vt;
    vt.datetime = sl.at(VOLT_DATETIME);
    vt.username = sl.at(VOLT_USERNAME);
    vt.lowVolt.setValue = sl.at(VOLT_LOW_VOLT_SET).toDouble();
    vt.lowVolt.realValue = sl.at(VOLT_LOW_VOLT_REAL).toDouble();
    vt.mediumVolt.setValue = sl.at(VOLT_MEDIUM_VOLT_SET).toDouble();
    vt.mediumVolt.realValue = sl.at(VOLT_MEDIUM_VOLT_REAL).toDouble();
    vt.highVolt.setValue = sl.at(VOLT_HIGH_VOLT_SET).toDouble();
    vt.highVolt.realValue = sl.at(VOLT_HIGH_VOLT_REAL).toDouble();
    vt.lowPulseWidth.setValue = sl.at(VOLT_LOW_PULSE_WIDTH_SET).toDouble();
    vt.lowPulseWidth.realValue = sl.at(VOLT_LOW_PULSE_WIDTH_REAL).toDouble();
    vt.mediumPulseWidth.setValue = sl.at(VOLT_MEDIUM_PULSE_WIDTH_SET).toDouble();
    vt.mediumPulseWidth.realValue = sl.at(VOLT_MEDIUM_PULSE_WIDTH_REAL).toDouble();
    vt.highPulseWidth.setValue = sl.at(VOLT_HIGH_PULSE_WIDTH_SET).toDouble();
    vt.highPulseWidth.realValue = sl.at(VOLT_HIGH_PULSE_WIDTH_REAL).toDouble();
    return vt;
}

EttaError SqlHelper::insertFlowTestResult(FlowTest st)
{
    EttaError ret = EttaError::OK;
    QString qsQuery = QString("INSERT INTO %1 VALUES(?,?,?,?,?)").arg(tables[T_FLOW_TEST]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(FLOW_DATETIME, st.datetime);
    query.bindValue(FLOW_USERNAME, st.username);
    query.bindValue(FLOW_VOLUME_SET, st.volume);
    query.bindValue(FLOW_SET, st.flow.setValue);
    query.bindValue(FLOW_REAL, st.flow.realValue);
    ret = insertTable(query);
    mutex.unlock();
    return ret;
}

QList<FlowTest> SqlHelper::selectFlowTestBasedOnTime(QString startTime, QString endTime, QString user)
{
    QList<FlowTest> flowList;
    QList<QStringList> listInfo;
    QString qsQuery = QString("select * from %1 where DATETIME(datetime) >= ? AND DATETIME(datetime) <= ?").arg(tables[T_FLOW_TEST]);
    if (!user.isEmpty()) {
        if (user.startsWith("!="))
        {
            qsQuery += " AND username != '";
            qsQuery += user.replace("!=", "") + "'";
        }
        else
        {
            qsQuery += " AND username = '";
            qsQuery += user + "'";
        }
    }

    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0,startTime);
    query.bindValue(1,endTime);
    listInfo = selectFromTable(query);
    mutex.unlock();

    for (int i = 0;i < listInfo.size();i++) {
        FlowTest tmp = convertSL2FlowTest(listInfo[i]);;
        flowList.append(tmp);
    }
    return flowList;
}

FlowTest SqlHelper::convertSL2FlowTest(QStringList sl)
{
    FlowTest st;
    st.datetime = sl.at(FLOW_DATETIME);
    st.username = sl.at(FLOW_USERNAME);
    st.volume = sl.at(FLOW_VOLUME_SET).toDouble();
    st.flow.setValue = sl.at(FLOW_SET).toDouble();
    st.flow.realValue = sl.at(FLOW_REAL).toDouble();
    return st;
}

EttaError SqlHelper::insertLogInfo(LogInfo logInfo)
{
    EttaError ret = EttaError::OK;
    QString qsQuery = QString("INSERT INTO %1 VALUES(?,?,?,?)").arg(tables[T_LOG]);
    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(LOG_DATETIME, logInfo.datetime);
    query.bindValue(LOG_USERNAME, logInfo.username);
    query.bindValue(LOG_TYPE, logInfo.type);
    query.bindValue(LOG_DETAIL, logInfo.detail);
    ret = insertTable(query);
    mutex.unlock();
    return ret;
}

QList<LogInfo> SqlHelper::selectLogTimeAndType(LogInfo logInfo, QString startTime, QString endTime, QString user)
{
    QList<LogInfo> listLog;
    QList<QStringList> listInfo;
    QString qsQuery = QString("select * from %1 where type=? and DATETIME(datetime) >= ? AND DATETIME(datetime) <= ?").arg(tables[T_LOG]);
    if (!user.isEmpty()) {
        if (user.startsWith("!="))
        {
            qsQuery += " AND username != '";
            qsQuery += user.replace("!=", "") + "'";
        }
        else
        {
            qsQuery += " AND username = '";
            qsQuery += user + "'";
        }
    }

    mutex.lock();
    QSqlQuery query;
    query.prepare(qsQuery);
    query.bindValue(0,logInfo.type);
    query.bindValue(1,startTime);
    query.bindValue(2,endTime);
    listInfo = selectFromTable(query);
    mutex.unlock();

    for (int i = 0; i < listInfo.size();i++) {
        LogInfo tmp = convertSL2Loginfo(listInfo[i]);
        listLog.append(tmp);
    }
    return listLog;
}

LogInfo SqlHelper::convertSL2Loginfo(QStringList sl)
{
    LogInfo logInfo;
    logInfo.datetime = sl.at(LOG_DATETIME);
    logInfo.username = sl.at(LOG_USERNAME);
    logInfo.type =(LogType)sl.at(LOG_TYPE).toInt();
    logInfo.detail = sl.at(LOG_DETAIL);
    return logInfo;
}

